﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using Entity;


namespace Web.admin.BasicManage.Classes
{   
    public partial class CLA_InserAll : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        /// <summary>
        /// 批量上传事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void btn_up_Click(object sender, EventArgs e)
        {
            BLL.ClassService classservice = new BLL.ClassService();
            BLL.AcademyService academyservice = new BLL.AcademyService();
            BLL.MajorService majorservice = new BLL.MajorService();
            BLL.LocalService localservice = new BLL.LocalService();
            //判断是否选中文件
            if (ExcelUpLoad.HasFile)
            {
                //得到文件类型
                string fileClass = System.IO.Path.GetExtension(ExcelUpLoad.FileName).ToString();
                if (fileClass == ".xls" || fileClass == ".xlsx")
                {
                    string SavaPath = MapPath("../../Upload").ToString();
                    //如果不存在此路径，则创建
                    if (!System.IO.Directory.Exists(SavaPath))
                    {
                        System.IO.Directory.CreateDirectory(SavaPath);
                    }
                    string name = ExcelUpLoad.FileName;
                    DateTime dtime = DateTime.Now;
                    string newname = dtime.ToString("yyyyMMddHHmmssff") + fileClass;
                    //保存完整路径
                    SavaPath = SavaPath + "\\" + newname;
                    //保存文件
                    ExcelUpLoad.SaveAs(SavaPath);
                    DataTable dt = ExcelToDb(SavaPath, "Sheet1");
                    BoClasses Model = new BoClasses();
                    int count = 1;
                    //excel的格式要求，班级编码,班级名称,班级简称,班级英文名,所属学院,学生人数,入学日期,专业,学制类型,是否显示,备注,校区
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (!string.IsNullOrEmpty(dr["班级编号"].ToString()))
                        {
                            if (classservice.GetClassByCodeAndMajorCodeAndAcademyName(dr["班级编号"].ToString(), dr["专业编号"].ToString(), dr["院系名"].ToString()) == null)
                                Model.CLA_CODE = dr["班级编号"].ToString();
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该班级已经存在')</script>");
                                return;
                            }
                        }
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,班级编码不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                         if (!string.IsNullOrEmpty(dr["班级名称"].ToString()))
                             Model.CLA_NAME = dr["班级名称"].ToString();
                        else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,班级名称不允许为空，请检查后再次导入')</script>");
                            return;
                        }
                         if (!string.IsNullOrEmpty(dr["班级简称"].ToString()))
                            Model.CLA_SHORT = dr["班级简称"].ToString();
                        else
                        {
                             Model.CLA_SHORT ="";
                        } 
                        if (!string.IsNullOrEmpty(dr["班级英文名"].ToString()))
                            Model.CLA_ENNAME = dr["班级英文名"].ToString();
                        else
                        {
                           Model.CLA_ENNAME="";
                        }
                         if (!string.IsNullOrEmpty(dr["院系名"].ToString()))
                         {
                             IList<BoAcademy> academyModel = academyservice.getAcademyByName(dr["院系名"].ToString());
                             if (academyModel != null && academyModel.Count>0)
                                 Model.CLA_ACADEMY = academyModel[0].ACA_ID;
                             else
                             {
                                 Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该学院名称不正确')</script>");
                                 return;
                             }
                         }
                         else
                         {
                             Response.Write("<script>alert('第" + (count + 1) + "行出现错误,学院名称不允许为空')</script>");
                             return;
                         }
                         if (!string.IsNullOrEmpty(dr["班级人数"].ToString()))
                             Model.CLA_STUNUM = Convert.ToInt32(dr["班级人数"].ToString());
                        else
                        {
                           Model.CLA_STUNUM=0;
                        }
                        if (!string.IsNullOrEmpty(dr["入学日期"].ToString()))
                            Model.CLA_INDATE = DateTime.Parse(dr["入学日期"].ToString());
                        if (!string.IsNullOrEmpty(dr["专业编号"].ToString()))
                        {
                            if (majorservice.GetMajorsByCode(dr["专业编号"].ToString()) != null)
                                Model.CLA_MAJOR = dr["专业编号"].ToString();
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该专业编号不存在')</script>");
                                return;
                            }
                        }       
                         else
                        {
                            Response.Write("<script>alert('第" + (count + 1) + "行出现错误,编号不允许为空')</script>");
                            return;
                        }
                        if (!string.IsNullOrEmpty(dr["学制类型"].ToString()))
                            Model.CLA_SYSTYPE =dr["学制类型"].ToString();
                         else
                        {
                           Model.CLA_SYSTYPE="";
                        }
                        if (!string.IsNullOrEmpty(dr["校区"].ToString()))
                        {
                            BoLocal localmodel = localservice.GetLocalByName(dr["校区"].ToString());
                            if (localmodel != null)
                                Model.CLA_LOCAL = localmodel.LOC_ID;
                            else
                            {
                                Response.Write("<script>alert('第" + (count + 1) + "行出现错误,该校区不存在')</script>");
                                return;
                            }
                        }
                        else
                        {
                            Model.CLA_LOCAL = "";
                        }
                         if (!string.IsNullOrEmpty(dr["备注"].ToString()))
                            Model.CLA_REMARK =dr["备注"].ToString();
                         else
                        {
                           Model.CLA_REMARK="";
                        }
                         classservice.CreateClass(Model);
                        count++;
                    }
                    Response.Write("<script>alert('添加成功，共添加" + (count-1).ToString() + "条数据')</script>");

                }
                else
                    //当然这个工作可以在前台做，可以用javascript来做，给upfileload添加一个事件即可
                    Response.Write("<script>alert('请选择正确的excel表格')</script>");
            }
        }
        

        /// <summary>
        /// 将excel表格用access数据库打开
        /// </summary>
        /// <param name="ExcelFileName">文件路径</param>
        /// <param name="ExcelSheet">工作表名称</param>
        /// <returns>返回excel中表格数据</returns>
        protected DataTable ExcelToDb(string ExcelFileName, string ExcelSheet)
        {
            //犹如sqlserver中的sqlconnection
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ExcelFileName + ";" + "Extended Properties=Excel 5.0;";
            //犹如sqlstring，查询语句
            string strExcel = string.Format("select * from [{0}$]", ExcelSheet);
            DataTable dt = new DataTable();
            using (OleDbConnection con = new OleDbConnection(strCon))
            {
                OleDbDataAdapter odda = new OleDbDataAdapter(strExcel, con);
                odda.Fill(dt);
                return dt;
            }
        }


        /// <summary>
        /// 单独添加班级事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void AddClass_Click(object sender, EventArgs e)
        { 
            
        }
    }
}